﻿/**

*/
using CRL.Core.Extension;
using CRL.Data.Attribute;
using CRL.Data.DBAccess;
using CRL.Data.LambdaQuery;
using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CRL.Data.DBAdapter
{
    public class PostgerSQLDBAdapter : DBAdapterBase
    {
        public PostgerSQLDBAdapter(DbContextInner _dbContext)
            : base(_dbContext)
        {
        }
        public override bool CanCompileSP
        {
            get
            {
                return false;
            }
        }
        #region 创建结构

        /// <summary>
        /// 创建存储过程脚本
        /// </summary>
        /// <param name="spName"></param>
        /// <returns></returns>
        public override string GetCreateSpScript(string spName, string script)
        {
            throw new NotSupportedException();
        }

        /// <summary>
        /// 获取字段类型映射
        /// </summary>
        /// <returns></returns>
        public override Dictionary<Type, string> FieldMaping()
        {
            Dictionary<Type, string> dic = new Dictionary<Type, string>();
            //字段类型对应
            dic.Add(typeof(System.String), "varchar({0})");
            dic.Add(typeof(System.Decimal), "decimal");
            dic.Add(typeof(System.Double), "double precision");
            dic.Add(typeof(System.Single), "real");
            dic.Add(typeof(System.Boolean), "boolean");
            dic.Add(typeof(System.Int32), "integer");
            dic.Add(typeof(System.Int16), "integer");
            dic.Add(typeof(System.Enum), "smallint");
            dic.Add(typeof(System.Byte), "smallint");
            dic.Add(typeof(System.DateTime), "timestamp");
            dic.Add(typeof(System.UInt16), "integer");
            dic.Add(typeof(System.Int64), "bigint");
            dic.Add(typeof(System.Object), "TEXT");
            dic.Add(typeof(System.Byte[]), "bytea");
            dic.Add(typeof(System.Guid), "uuid");
            return dic;
        }
        /// <summary>
        /// 获取列类型和默认值
        /// </summary>
        /// <param name="info"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        public override string GetColumnType(FieldInnerAttribute info, out string defaultValue)
        {
            Type propertyType = info.PropertyType;
            if (info.ValueNeedConvert)
            {
                propertyType = typeof(string);
                info.Length = 8000;
            }
            //Dictionary<Type, string> dic = GetFieldMaping();
            defaultValue = info.DefaultValue;

            //int默认值
            if (string.IsNullOrEmpty(defaultValue))
            {
                if (!info.IsPrimaryKey && propertyType.IsNumeric())
                {
                    defaultValue = "0";
                }
                //datetime默认值
                if (propertyType == typeof(System.DateTime))
                {
                    defaultValue = "NOW()";
                }
            }
            string columnType;
            columnType = GetDBColumnType(propertyType);
            //超过3000设为ntext
            if (propertyType == typeof(System.String) && info.Length > 3000)
            {
                //columnType = "ntext";
            }
            if (info.Length > 0)
            {
                columnType = string.Format(columnType, info.Length);
            }
            if (info.IsPrimaryKey)
            {
                if (info.KeepIdentity == true)
                {
                    columnType = columnType + " ";
                }
                else
                {
                    ////todo 只有数值型才能自增
                    //if (info.PropertyType != typeof(string))
                    //{
                    //    columnType = columnType + " IDENTITY(1,1) ";
                    //}
                }
            }
            if (!string.IsNullOrEmpty(info.ColumnType))
            {
                columnType = info.ColumnType;
            }
            return columnType;
        }

        /// <summary>
        /// 创建字段脚本
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        public override string GetCreateColumnScript(DbContextInner dbContext, FieldInnerAttribute field)
        {
            var table = TypeCache.GetTable(field.ModelType);
            var tableName = TypeCache.GetTableName(table.TableName, dbContext);
            var columnType = GetColumnType(field, out var defaultValue);
            string str = string.Format("alter table {0} add {1} {2}", KeyWordFormat(tableName), KeyWordFormat(field.MapingName), columnType);
            if (!string.IsNullOrEmpty(defaultValue))
            {
                str += string.Format(" default {0}", defaultValue);
            }
            if (field.NotNull)
            {
                str += " not null";
            }
            //else if (field.PropertyType == typeof(string))
            //{
            //    str += " COLLATE NOCASE";
            //}
            return str;
        }

        public override string GetCreateIndexScript(string owner, TableInnerAttribute table, bool unique, string indexName, params string[] columns)
        {
            var tableName = KeyWordFormat(table.TableName);
            var index = unique ? "unique index" : "index";
            var script = $"create {index} {indexName} on {tableName} ({string.Join(",", columns.ToArray())});";
            return script;
        }
        /// <summary>
        /// 创建表脚本
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override void CreateTable(DbContextInner dbContext, List<FieldInnerAttribute> fields, string tableName)
        {
            string script = string.Format("create table public.{0} (\r\n", KeyWordFormat(tableName));
            List<string> list2 = new List<string>();
            foreach (FieldInnerAttribute item in fields)
            {
                var columnType = GetColumnType(item, out var defaultValue);
                var str = $"{KeyWordFormat(item.MapingName)} {columnType}";
                if (item.IsPrimaryKey)
                {
                    if (item.PropertyType.IsNumeric())
                    {
                        str = $"{KeyWordFormat(item.MapingName)} serial";
                    }
                    str += " primary key";
                }
                if (item.NotNull)
                {
                    str += " NOT NULL";
                }
                if (!string.IsNullOrEmpty(defaultValue))
                {
                    str += $" DEFAULT {defaultValue}";
                }
                list2.Add(str);
            }
            script += string.Join(",\r\n", list2.ToArray());
            script += ");";
            var helper = dbContext.DBHelper;
            helper.Execute(script);
        }
        #endregion
        public override DBType DBType
        {
            get { return DBType.NPGSQL; }
        }
        #region SQL查询

        public override string GetTableFields(string tableName)
        {
            return $"SELECT column_name as name,data_type as type FROM information_schema.columns As c WHERE table_name = '{tableName}'";
        }
        //static System.Collections.Concurrent.ConcurrentDictionary <string, DataTable> cacheTables = new System.Collections.Concurrent.ConcurrentDictionary<string, DataTable>();
        protected object valueFormat(FieldInnerAttribute info, object obj)
        {
            object value = info.GetValue(obj);
            value = ObjectConvert.CheckNullValue(value, info.PropertyType);
            if (info.ValueNeedConvert)
            {
                value = SettingConfig.ValueObjSerializer(value);
            }
            return value;
        }
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="details"></param>
        /// <param name="keepIdentity"></param>
        public override void BatchInsert(DbContextInner dbContext, System.Collections.IList details, bool keepIdentity = false)
        {
            if (details.Count == 0)
                return;
            var helper = dbContext.DBHelper;
            var sql = GetBatchInsertSql(dbContext, details, keepIdentity);
            helper.Execute(sql);
        }
        public override void InsertOrUpdate(DbContextInner dbContext, IList items, InsertOrUpdateOption option)
        {
            if (items.Count == 0)
                return;
            option = option ?? new InsertOrUpdateOption();
            var type = items[0].GetType();
            var table = TypeCache.GetTable(type);
            //if (table.PrimaryKey == null)
            //{
            //    throw new Exception($"InsertOrUpdate {table.Type} 缺少主键");
            //}
            //https://blog.csdn.net/neweastsun/article/details/112147693
            var tableName = KeyWordFormat(TypeCache.GetTableName(table.TableName, dbContext));
            var sb = new StringBuilder();
            string getFields(IEnumerable<FieldInnerAttribute> fields)
            {
                return string.Join(",", fields.Select(b => $"{KeyWordFormat(b.MapingName)}"));
            }
            var updateFields = table.Fields.Where(b => !b.IsPrimaryKey);
            if (option?.UpdateMemberNames?.Any() == true)
            {
                updateFields = updateFields.Where(b => option.UpdateMemberNames.Contains(b.MemberName));
            }
            if (string.IsNullOrEmpty(option.ConstraintMemberName))
            {
                throw new Exception($"缺少参数ConstraintMemberName");
            }
            var pIndex = 0;
            string getFieldValues(object item, IEnumerable<FieldInnerAttribute> fields)
            {
                var values = new List<string>();
                foreach (var f in fields)
                {
                    pIndex++;
                    var v = f.GetValue(item);
                    if (v is Enum)
                    {
                        v = Convert.ToInt32(v);
                    }
                    //var pName = GetParamName(f.MemberName, pIndex);
                    //dbContext.DBHelper.AddParam(pName, v);
                    var str = f.PropertyType.IsNumeric() ? $"{v}" : $"'{v}'";
                    values.Add(str);
                }
                return string.Join(",", values);
            }

            sb.Append($"INSERT INTO {tableName}({getFields(updateFields)})  values ");
            var i = 0;
            foreach (var item in items)
            {
                sb.Append($"({getFieldValues(item, updateFields)})");
                if (i < items.Count - 1)
                {
                    sb.Append(",");
                }
                i += 1;
            }
            sb.Append($"ON CONFLICT ({KeyWordFormat(option.ConstraintMemberName)})");
            sb.Append("\r\n DO NOTHING;");
            if (option.IfExistsNotUpdate)//已经存在的不更新
            {
            }
            //DO 
            //UPDATE SET email = EXCLUDED.email || ';' || customers.email;
            option.SqlOut = sb.ToString();
            dbContext.DBHelper.Execute(option.SqlOut);
        }

        public override async Task BatchInsertAsync(DbContextInner dbContext, IList details, bool keepIdentity = false)
        {
            if (details.Count == 0)
                return;
            var helper = dbContext.DBHelper;
            var sql = GetBatchInsertSql(dbContext, details, keepIdentity);
            await helper.ExecuteAsync(sql);
        }

        /// <summary>
        /// 插入对象,并返回主键
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public override object InsertObject<T>(DbContextInner dbContext, T obj)
        {
            Type type = obj.GetType();
            var helper = dbContext.DBHelper;
            helper.ClearParams();
            var table = TypeCache.GetTable(type);
            var primaryKey = table.PrimaryKey;
            var tableName = TypeCache.GetTableName(table.TableName, dbContext);
            var sql = GetInsertSql(dbContext, table, obj);
            if (primaryKey == null)
            {
                SqlStopWatch.Execute(helper, sql);
                return null;
            }
            if (primaryKey.KeepIdentity == true)
            {
                SqlStopWatch.Execute(helper, sql);
                return primaryKey.GetValue(obj);
            }
            else
            {
                return 0;//todo
                //sql += $";select LAST_INSERT_ROWID() FROM [{tableName}]";
                //return SqlStopWatch.ExecScalar(helper, sql);
            }
        }
        public override async Task<object> InsertObjectAsync<T>(DbContextInner dbContext, T obj)
        {
            Type type = obj.GetType();
            var helper = dbContext.DBHelper;
            helper.ClearParams();
            var table = TypeCache.GetTable(type);
            var primaryKey = table.PrimaryKey;
            var tableName = TypeCache.GetTableName(table.TableName, dbContext);
            var sql = GetInsertSql(dbContext, table, obj);
            if (primaryKey == null)
            {
                await SqlStopWatch.ExecuteAsync(helper, sql);
                return null;
            }
            if (primaryKey.KeepIdentity == true)
            {
                await SqlStopWatch.ExecuteAsync(helper, sql);
                return primaryKey.GetValue(obj);
            }
            else
            {
                return 0;//todo
                //sql += $";select LAST_INSERT_ROWID() FROM [{tableName}]";
                //return await SqlStopWatch.ExecScalarAsync(helper, sql);
            }
        }
        /// <summary>
        /// 获取 with(nolock)
        /// </summary>
        /// <returns></returns>
        public override string GetWithNolockFormat(bool v)
        {
            return "";
        }
        /// <summary>
        /// 获取前几条语句
        /// </summary>
        /// <param name="fields">id,name</param>
        /// <param name="query">from table where 1=1</param>
        /// <param name="sort"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public override void GetSelectFull(StringBuilder sb, string fields, Action<StringBuilder> query, string sort, int top)
        {
            sb.Append("select ");
            sb.Append(fields);
            query(sb);
            if (!string.IsNullOrEmpty(sort))
            {
                sb.Append(sort);
            }
            sb.Append(top == 0 ? "" : $" LIMIT {top}" );
        }
        #endregion

        #region 系统查询
        public override string GetAllTablesSql(string db)
        {
            return "select tablename , tablename from pg_tables where schemaname='public'";
        }
        public override string GetAllSPSql(string db)
        {
            throw new NotSupportedException();
            return "select name,id from sysobjects where  type='P'";
        }
        #endregion

        #region 模版
        public override string SpParameFormat(string name, string type, bool output)
        {
            throw new NotSupportedException();
        }
        static ConcurrentDictionary<string, string> KeyWordFormatCache = new ConcurrentDictionary<string, string>();
        public override string KeyWordFormat(string value)
        {
            return KeyWordFormatCache.GetOrAdd(value, $"\"{value}\"");
            //return value.ToLower();
            return $"\"{value}\"";
        }
        //public override string FieldNameFormat(FieldAttribute field)
        //{
        //    if(string.IsNullOrEmpty(field.MapingNameFormat))
        //    {
        //        return field.MapingName;
        //    }
        //    return field.MapingNameFormat;
        //}
        public override string TemplateGroupPage
        {
            get
            {
                throw new NotSupportedException();

            }
        }

        public override string TemplatePage
        {
            get
            {
                throw new NotSupportedException();
            }
        }

        public override string TemplateSp
        {
            get
            {
                throw new NotSupportedException();
            }
        }
        public override string SqlFormat(string sql)
        {
            return sql;
        }
        #endregion

        #region 函数格式化
        public override string SubstringFormat(string field, int index, int length)
        {
            return string.Format(" substr({0},{1},{2})", field, index + 1, length);
        }

        public override string StringLikeFormat(string field, string parName)
        {
            return string.Format("{0} LIKE {1}", field, parName);
        }

        public override string StringNotLikeFormat(string field, string parName)
        {
            return string.Format("{0} NOT LIKE {1}", field, parName);
        }

        public override string StringContainsFormat(string field, string parName)
        {
            return $"{field} like concat('%', {parName}, '%')";
        }
        public override string StringNotContainsFormat(string field, string parName)
        {
            return $"{field} not like concat('%', {parName}, '%')";
        }
        public override string BetweenFormat(string field, string parName, string parName2)
        {
            return string.Format("{0} between {1} and {2}", field, parName, parName2);
        }

        public override string DateDiffFormat(string field, string format, string parName)
        {
            return "0";
            //throw new NotSupportedException("DateDiff");
            return string.Format("DateDiff({0},{1},{2})", format, field, parName);
        }

        public override string InFormat(string field, string parName)
        {
            return string.Format("{0} IN ({1})", field, parName);
        }
        public override string NotInFormat(string field, string parName)
        {
            return string.Format("{0} NOT IN ({1})", field, parName);
        }
        #endregion

        public override string PageSqlFormat(DBHelper db, string fields, string rowOver, string condition, int start, int end, string sort)
        {
            start -= 1;
            if (start < 0)
            {
                start = 0;
            }
            db.AddParam(":start", start);
            db.AddParam(":row", end - start);
            string sql = "select {0} {1} {4} LIMIT {2} OFFSET {3} ";
            return string.Format(sql, fields, condition, ":start", ":row", string.IsNullOrEmpty(sort) ? "" : "order by " + sort);
        }
        public override string GetRelationUpdateSql(string t1, string t2, string condition, string setValue, LambdaQueryBase query)
        {
            //to test
            condition = Regex.Replace(condition, @".+?join.+?on (.+)", "$1", RegexOptions.IgnoreCase);
            condition = condition.Replace("t1.", $"{KeyWordFormat(t1)}.");
            setValue = setValue.Replace("t1.", $"");
            return $"update {KeyWordFormat(t1)} set {setValue} from {t2} t2 where {condition}";
        }
        public override string GetRelationDeleteSql(string t1, string t2, string condition, LambdaQueryBase query)
        {
            //string table = string.Format("{0} t1", KeyWordFormat(t1), KeyWordFormat(t2));
            //string sql = string.Format("delete t1 from {0} {1}", table, condition);
            //return sql;
            // Inner join mm t2 on t1.supplierid=:p1 AND t2.b=:p2 where (t1.id=:p0)
            condition = condition.Replace("where", "and");
            condition = Regex.Replace(condition, @"\w+ join.+?on", "");
            return $"DELETE FROM {KeyWordFormat(t1)} t1 USING {KeyWordFormat(t2)} t2 where{condition}";
        }
        public override string GetFieldConcat(string field, object value, Type type)
        {
            string str;
            if (type == typeof(string))
            {
                str = string.Format("{0}+'{1}'", field, value);
            }
            else
            {
                str = string.Format("{0}+{1}", field, value);
            }
            return str;
        }
        public override string CastField(string field, Type fieldType)
        {
            var dic = FieldMaping();
            if (!dic.ContainsKey(fieldType))
            {
                throw new Exception(string.Format("没找到对应类型的转换{0} 在字段{1}", fieldType, field));
            }
            var type = dic[fieldType];
            type = string.Format(type, 100);
            return string.Format("CAST({0} as {1})", field, type);
        }
        public override string GetParamName(string name, object index)
        {
            return string.Format(":{0}{1}", name, index);
        }

        public override string DateTimeFormat(string field, string format)
        {
            return $"TO_CHAR({KeyWordFormat(field)}, '{format}')";
        }
        public override string GetSplitFirst(string field, string parName)
        {
            return "'NotSupported'";
            //throw new NotSupportedException("SplitFirst");
            return $"substring({field},1,charindex('{parName}',{field})-1)";
        }
        public override Dictionary<string, long> GetFieldLength(DbContextInner dbContext, string tableName)
        {
            return new Dictionary<string, long>();
        }
        public override string GetUpdateColumnScript(string tableName, FieldInnerAttribute field)
        {
            var columnType = GetDBColumnType(field.PropertyType);
            if (field.Length > 0)
            {
                columnType = string.Format(columnType, field.Length);
            }
            var str = GetDropColumnScript(tableName, field.MapingName);
            return str + $";alter table {KeyWordFormat(tableName)} add {KeyWordFormat(field.MapingName)} {columnType}";
        }
    }
}
